package training.osms.persistence.hibernate;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Order;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.lang.StringUtils;

import training.osms.business.model.Category;
import training.osms.business.model.CategorySearchOptions;
import training.osms.business.model.Category_;
import training.osms.persistence.ICategoryDAO;

public class CategoryDAO implements ICategoryDAO {

	private EntityManagerFactory factory;
	
	public CategoryDAO(){
		this.factory = Persistence.createEntityManagerFactory("osms");
	}
	
	@Override
	public void saveCategory(Category category) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = null;
		
		try{	
			transaction = manager.getTransaction();
			transaction.begin();
			manager.persist(category);
			manager.flush();
			transaction.commit();
		}catch(RuntimeException e){
			if(transaction != null)
				try{
					transaction.rollback();
				}catch(RuntimeException nestedException){}
			throw e;
		}finally{
			if(manager != null)
				try{
					manager.close();
				}catch(RuntimeException e){}
		}

	}

	@Override
	public Category selectCategory(String categoryName) {
		StringBuilder jpql = new StringBuilder("select cat from ");
		jpql.append(Category.class.getName());
		jpql.append(" cat where trim(lower(cat.name)) = :categoryName");
		
		
		EntityManager manager = null;
		try{	
			manager = factory.createEntityManager();
			TypedQuery<Category> query = manager.createQuery(jpql.toString(), Category.class);
			query.setParameter("categoryName", categoryName.toLowerCase().trim());
			List<Category> result = query.getResultList();
			if(result.isEmpty()){
				return null;
			}else{
				return result.get(0);
			}

		}finally{
			if(manager != null)
				try{
					manager.close();
				}catch(RuntimeException e){}
		}
	}
	
	@Override
	public List<Category> selectCategory(CategorySearchOptions options){
		return selectCategoryCriteria(options);
	}
	
	public List<Category> selectCategoryJpql(CategorySearchOptions options){
		StringBuilder jpql = new StringBuilder("select cat from ");
		jpql.append(Category.class.getName());
		jpql.append(" cat where 1=1 ");
		
		String categoryName = options.getName();
		if(categoryName != null && categoryName.trim().length() > 0){
			jpql.append(" and lower(cat.name) like :categoryName ");
		}
		
		String categoryDescription = options.getDescription();
		if(categoryDescription != null && categoryDescription.trim().length() > 0){
			jpql.append(" and lower(cat.description) like :categoryDescription ");
		}
		
		jpql.append(" order by cat.name ");
		
		
		EntityManager manager = null;
		try{	
			manager = factory.createEntityManager();
			TypedQuery<Category> query = manager.createQuery(jpql.toString(), Category.class);
			
			if(categoryName != null && categoryName.trim().length() > 0){
				query.setParameter("categoryName", toLikedParameter(categoryName));
			}
			if(categoryDescription != null && categoryDescription.trim().length() > 0){
				query.setParameter("categoryDescription", toLikedParameter(categoryDescription));
			}
			
			if(options.getFirstResult() != null)
				query.setFirstResult(options.getFirstResult());
			
			if(options.getMaxResult() != null)
				query.setMaxResults(options.getMaxResult());
			
			List<Category> result = query.getResultList();
			return result;

		}finally{
			if(manager != null)
				try{
					manager.close();
				}catch(RuntimeException e){}
		}
	}
	
	public List<Category> selectCategoryCriteria(CategorySearchOptions options){
		
			
		EntityManager manager = null;
		try{	
			manager = factory.createEntityManager();
			CriteriaBuilder builder = manager.getCriteriaBuilder();
			CriteriaQuery<Category> criteriaQuery = builder.createQuery(Category.class);
			Root<Category> root = criteriaQuery.from(Category.class);
			
			criteriaQuery.select(root);
			criteriaQuery.where(toPredicate(builder, root, options));
			Order order = builder.asc(root.get(Category_.name));
			criteriaQuery.orderBy(order);
			
			TypedQuery<Category> query = manager.createQuery(criteriaQuery);
			
			if(options.getFirstResult() != null)
				query.setFirstResult(options.getFirstResult());
			
			if(options.getMaxResult() != null)
				query.setMaxResults(options.getMaxResult());
			
			List<Category> result = query.getResultList();
			return result;

		}finally{
			if(manager != null)
				try{
					manager.close();
				}catch(RuntimeException e){}
		}
		
	}
	
	public Integer selectCategoryCount(CategorySearchOptions options){
		EntityManager manager = null;
		try{	
			manager = factory.createEntityManager();
			CriteriaBuilder builder = manager.getCriteriaBuilder();
			CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
			Root<Category> root = criteriaQuery.from(Category.class);
			
			criteriaQuery.select(builder.count(root));
			criteriaQuery.where(toPredicate(builder, root, options));
			
			TypedQuery<Long> query = manager.createQuery(criteriaQuery);
	
			Long result = query.getSingleResult();
			return result.intValue();

		}finally{
			if(manager != null)
				try{
					manager.close();
				}catch(RuntimeException e){}
		}
	}
	
	private Predicate toPredicate(CriteriaBuilder builder, Root<Category> root, CategorySearchOptions options){
		
		Predicate categoryParentPredicate;
		Integer categoryParentIdentifier = options.getCategoryParentIdentifier();
		
		if(categoryParentIdentifier == null || categoryParentIdentifier == 0)
			categoryParentPredicate = builder.conjunction();
		else
			categoryParentPredicate = builder.equal(root.get(Category_.categoryParent).get(Category_.identifier), categoryParentIdentifier);
		
		String categoryName = options.getName();
		String categoryDescription = options.getDescription();
		Predicate namePredicate;
		if(!StringUtils.isBlank(categoryName)){
			namePredicate = builder.like(
					builder.lower(root.get(Category_.name)), 
					toLikedParameter(categoryName));
		}
		else{
			namePredicate = builder.conjunction();
		}
		
		Predicate descriptionPredicate;
		if(!StringUtils.isBlank(categoryDescription)){
			descriptionPredicate = builder.like(
					builder.lower(root.get(Category_.description)), 
					toLikedParameter(categoryDescription));
		}
		else{
			descriptionPredicate = builder.conjunction();
		}
		
		
		Predicate predicate = builder.and(namePredicate, descriptionPredicate, categoryParentPredicate); 
		return predicate;
	}
	
	@Override
	public void updateCategory(Category category) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = null;
		
		try{	
			transaction = manager.getTransaction();
			transaction.begin();
			manager.merge(category);
			manager.flush();
			transaction.commit();
		}catch(RuntimeException e){
			if(transaction != null)
				try{
					transaction.rollback();
				}catch(RuntimeException nestedException){}
			throw e;
		}finally{
			if(manager != null)
				try{
					manager.close();
				}catch(RuntimeException e){}
		}
	}

	@Override
	public void deleteCategory(Category category) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = null;
		
		try{	
			transaction = manager.getTransaction();
			Category databaseCategory = manager.find(Category.class, category.getIdentifier());
			transaction.begin();
			manager.remove(databaseCategory);
			manager.flush();
			transaction.commit();
		}catch(RuntimeException e){
			if(transaction != null)
				try{
					transaction.rollback();
				}catch(RuntimeException nestedException){}
			throw e;
		}finally{
			if(manager != null)
				try{
					manager.close();
				}catch(RuntimeException e){}
		}
	}

	private String toLikedParameter(String parameter) {
		return "%" + parameter.toLowerCase().trim() + "%";
	}
}
